<!DOCTYPE html>
<html>
<head>
	<meta charset="utf-8">
	<title>MySQL to SQLite online converter 🔨</title>
	<style type="text/css">
		html,
		body {
			font-family: sans-serif;
			padding: 0;
			margin: 0;
			width: 100%;
			background: #999;
			text-align: center;
		}
		body {
			max-width: 1024px;
			margin: 1em auto;
		}
		textarea {
			display: block;
			width: 100%;
			height: 300px;
		}
		button {
			margin: 30px 0;
			padding: 5px 10px;
			font-size: 32px;
		}
		p {
			font-size: 20px;
		}
		a, a:visited {
			color: #00D;
		}
	</style>
</head>
<body>
	<script>
		function convert() {
			var sqlite = "" +
			"-- import to SQLite by running: sqlite3.exe db.sqlite3 -init sqlite.sql\n\n" +
			"PRAGMA journal_mode = MEMORY;\n" +
			"PRAGMA synchronous = OFF;\n" +
			"PRAGMA foreign_keys = OFF;\n" +
			"PRAGMA ignore_check_constraints = OFF;\n" +
			"PRAGMA auto_vacuum = NONE;\n" +
			"PRAGMA secure_delete = OFF;\n" +
			"BEGIN TRANSACTION;\n\n";

			var currentTable = '';

			var lines = document.getElementById('mysql').value.split('\n');
			var skip = [/^CREATE DATABASE/i, /^USE/i, /^\/\*/i, /^--/i];
			var keys = [];

			// Used this site to test regexes: https://regex101.com/

			lineLoop:
			for (var i = 0; i < lines.length; i++) {
				line = lines[i].trim();
				// Skip lines that match regexes in the skip[] array above
				for (var j = 0; j < skip.length; j++) if (skip[j].test(line)) continue lineLoop;
				// Include all `INSERT` lines. Replace \' by ''
				if (/^(INSERT|\()/i.test(line)) {
					sqlite += line.replace(/\\'/gi, "''") + "\n";
					continue;
				}
				// Print the ´CREATE´ line as is and capture the table name
				if ((m = /^\s*CREATE TABLE.*[`"](.*)[`"]/i.exec(line)) !== null) {
					currentTable = m[1];
					sqlite += "\n" + line + "\n";
					continue;
				}
				// Clean table end line like:
				// ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8 COMMENT='By definition:\r\n- user_group #1 is administrator and will always have all permissions.\r\n- user_group #2 is guest and always have no permissions.\r\n';
				if (line.startsWith(")")) {
					sqlite += ");\n";
					continue;
				}
				// Remove CONSTRAINT `fk_address_state1`" part from lines
				line = line.replace(/^CONSTRAINT [`'"][\w]+[`'"][\s]+/gi, '');
				// Replace "XXXXX KEY" by "KEY" except "PRIMARY KEY" "FOREIGN KEY" and "UNIQUE KEY"
				line = line.replace(/^[^FOREIGN][^PRIMARY][^UNIQUE]\w+\s+KEY/gi, 'KEY');
				
				// Lines starting with (UNIQUE) KEY are extracted so we declare them all at the end of the script
				// We also append key name with table name to avoid duplicate index name
				// Example: KEY `name` (`permission_name`)
				if ((m = /^(UNIQUE\s)*KEY\s+[`'"](\w+)[`'"]\s+\([`'"](\w+)[`'"]/ig.exec(line)) !== null) {
					keyUnique = m[1] || "";
					keyName = m[2];
					colName = m[3];
					keys.push('CREATE '+ keyUnique +'INDEX `'+ currentTable + '_' + keyName + '` ON `' + currentTable +'` (`' +  colName + '`);');
					continue;
				}
				// Print all fields definition lines except "KEY" lines and lines starting with ")"
				if (/^[^)]((?![\w]+\sKEY).)*$/gi.test(line)) {
					// Clear invalid keywords
					line = line.replace(/AUTO_INCREMENT|CHARACTER SET [^ ]+|CHARACTER SET [^ ]+|UNSIGNED/ig, "");
					line = line.replace(/DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|COLLATE [^ ]+/ig, "");
					line = line.replace(/COMMENT\s['"`].*['"`]/ig, "");
					line = line.replace(/SET\([^)]+\)|ENUM[^)]+\)/ig, "TEXT ");
					// Clear weird MySQL types such as varchar(40) and int(11)
					line = line.replace(/int\([0-9]*\)/ig, "INTEGER");
					line = line.replace(/varchar\([0-9]*\)|LONGTEXT/ig, "TEXT");
				}
				
				if (line != "") {
					sqlite += line + "\n";
				}				
			}
			sqlite += "\n";

			// Fix last table line with comma
			sqlite = sqlite.replace(/,\n\);/g, "\n);");

			// Include all gathered keys as CREATE INDEX
			sqlite += "\n\n" + keys.join("\n") + "\n\n";

			// Re-enable foreign key check
			sqlite += "COMMIT;\n" +
			"PRAGMA ignore_check_constraints = ON;\n" +
			"PRAGMA foreign_keys = ON;\n" +
			"PRAGMA journal_mode = WAL;\n" +
			"PRAGMA synchronous = NORMAL;\n";



			document.getElementById('sqlite').value = sqlite.replaceAll(/0x(\w+)/ig, "X'$1'");
		}
	</script>

	<h1>MySQL to SQLite online converter 🔨</h1>
	<textarea id="mysql">Paste MySQL SQL code here</textarea>

	<button onclick="convert()">Convert</button>

	<h1>SQLite result:</h1>
	<textarea id="sqlite"></textarea>

	<p>Client side only. No dependencies or server side processing. You can save this page for offline use.<br>
		Send requests, bug reports & suggestions to git repo: <a href="http://github.com/ww9/mysql2sqlite">http://github.com/ww9/mysql2sqlite</a>
		<br>License: <a href="http://unlicense.org/">The Unlicense</a>, <a href="https://gist.github.com/ww9/4c4481fb7b55186960a34266078c88b1">Public Domain</a>. As free as it gets.</p>
</body>

</html>
